package com.situ.company.util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.ResourceBundle;

public class JDBCUtil {

    // private static String className = "com.mysql.jdbc.Driver";
    // private static String url =
    // "jdbc:mysql://127.0.0.1:3306/sql_study?useSSL=false&useUnicode=true&characterEncoding=utf8";
    // private static String user = "root";
    // private static String password = "root";
    private static ResourceBundle rb = ResourceBundle.getBundle("com.situ.company.util.jdbc");

    private static String className = rb.getString("driver");
    private static String url       = rb.getString("url");
    private static String user      = rb.getString("user");
    private static String password  = rb.getString("pass");

    static {
        try {
            Class.forName(className);// 1、加载驱动：Class.forName("");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获得链接对象
     * 
     * @return Connection
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

    /**
     * 关闭连接
     * 
     * @param conn
     * @param st
     * @param rs
     */
    public static void close(Connection conn, Statement st, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (st != null) {
                st.close();
                st = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 关闭连接
     * 
     * @param connection
     * @param statement
     */
    public static void close(Connection connection, Statement statement) {
        close(connection, statement, null);
    }

    public static Integer update(String sql, Object... obj) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            ps = con.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++)
                ps.setObject(i + 1, obj[i]);
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(con, ps);
        }
        return null;
    }

    public static Integer update(String sql, List<Object> obj) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            ps = con.prepareStatement(sql);
            for (int i = 0; i < obj.size(); i++)
                ps.setObject(i + 1, obj.get(i));
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(con, ps);
        }
        return null;
    }

    /**
     * @param <T>
     * @param sql
     * @param vals
     * @param clazz
     * @param fields
     *            key是属性名，value是字段名
     * @return
     */
    public static <T> List<T> query(String sql, List<Object> vals, Class<T> clazz, Map<String, String> fields) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<T> result = new ArrayList<>();
        try {
            con = getConnection();
            ps = con.prepareStatement(sql.toString());
            for (int i = 0; i < vals.size(); i++)
                ps.setObject(i + 1, vals.get(i));
            rs = ps.executeQuery();
            while (rs.next()) {
                T obj = clazz.newInstance();
                for (Entry<String, String> map : fields.entrySet()) {
                    String key = map.getKey();
                    String val = map.getValue();
                    try {
                        Field field = clazz.getDeclaredField(key);
                        field.setAccessible(true);
                        field.set(obj, rs.getObject(val));
                    } catch (NoSuchFieldException | SecurityException e) {
                        e.printStackTrace();
                    }
                }
                result.add(obj);
            }
        } catch (SQLException | InstantiationException | IllegalAccessException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(con, ps, rs);
        }
        return result;
    }

    public static Integer queryInt(String sql, List<Object> vals) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = getConnection();
            ps = con.prepareStatement(sql.toString());
            for (int i = 0; i < vals.size(); i++)
                ps.setObject(i + 1, vals.get(i));
            rs = ps.executeQuery();
            if (rs.next())
                return rs.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.close(con, ps, rs);
        }
        return null;
    }

    public static void main(String[] args) throws SQLException {
        Connection con = JDBCUtil.getConnection();
        System.out.println(con);
        JDBCUtil.close(con, null);
        // Connection co = JDBCUtil.getConnection();
        // PreparedStatement st = co.prepareStatement("");
        // JDBCUtil.close(con, st);
        // ResultSet rs = st.executeQuery();
        // JDBCUtil.close(con, null, rs);
    }

    private JDBCUtil() {
    }

}
